/*
 * Books_Model.java
 * Final Project Example
 * Created on Oct 3, 2014, 05:26:13 PM
 * by Fairul Filza with Team FA_PemrogrammanLanjut
 */

package Models;

// <editor-fold defaultstate="collapsed" desc="Import Code">
import Database.DvdsRetail_Connection;
import Interfaces.ModelsProcess_Interface;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
// </editor-fold>

/** @author PemrogrammanLanjut_LABIV_TI_05~09 */
public class Dvds_Model implements ModelsProcess_Interface {
    private String DvdId;;
    private String Title;
    private String Detail;
    private String Search;
    private int Price;
    private int Stock;
    private Categories_Model Categories;
    private DvdsRetail_Connection Connex;
    private String[][] DataRecord;

    // <editor-fold defaultstate="collapsed" desc="Constructor Code">
    public Dvds_Model() {
        Connex = new DvdsRetail_Connection();
        Categories = new Categories_Model();
    }

    public Dvds_Model(String DvdId, String Title, String Detail, int Price, int Stock,
            String CategoryId) {
        Connex = new DvdsRetail_Connection();
        Categories = new Categories_Model();
        this.DvdId = DvdId;
        this.Title = Title;
        this.Detail = Detail;
        this.Price = Price;
        this.Stock = Stock;
        this.Categories.setCategoryId(CategoryId);
    }

    public Dvds_Model(String DvdId) {
        Connex = new DvdsRetail_Connection();
        Categories = new Categories_Model();
        this.DvdId = DvdId;
    }

    public Dvds_Model(String DvdId, int Stock) {
        Connex = new DvdsRetail_Connection();
        Categories = new Categories_Model();
        this.DvdId = DvdId;
        this.Stock = Stock;
    }
    
    public Dvds_Model(int code, String Search){
        Connex = new DvdsRetail_Connection();
        Categories = new Categories_Model();
        this.Search = Search;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Getter and Setter Code">
    public String getDetail() {
        return Detail;
    }

    public void setDetail(String Detail) {
        this.Detail = Detail;
    }

    public String getDvdId() {
        return DvdId;
    }

    public void setDvdId(String DvdId) {
        this.DvdId = DvdId;
    }

    public Categories_Model getCategories() {
        return Categories;
    }

    public void setCategories(Categories_Model Categories) {
        this.Categories = Categories;
    }

    public DvdsRetail_Connection getConnex() {
        return Connex;
    }

    public void setConnex(DvdsRetail_Connection Connex) {
        this.Connex = Connex;
    }

    public String[][] getDataRecord() {
        return DataRecord;
    }

    public void setDataRecord(String[][] DataRecord) {
        this.DataRecord = DataRecord;
    }

    public int getPrice() {
        return Price;
    }

    public void setPrice(int Price) {
        this.Price = Price;
    }

    public int getStock() {
        return Stock;
    }

    public void setStock(int Stock) {
        this.Stock = Stock;
    }

    public String getTitle() {
        return Title;
    }

    public void setTitle(String Title) {
        this.Title = Title;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Method Code">
    @Override
    public void showDataSet() {
        String Query;
        int i;

        Connex.Connection();
        try {
            Query = "SELECT a.Title, a.DvdId, b.Description, a.Stock, a.Price, a.Detail FROM Dvds a, Categories b "
                    + "WHERE a.CategoryId = b.CategoryId";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            List.last();
            DataRecord = new String[List.getRow()][6];
            List.beforeFirst();
            i = 0;
            while (List.next()) {
                DataRecord[i][0] = List.getString(1);
                DataRecord[i][1] = List.getString(2);
                DataRecord[i][2] = List.getString(3);
                DataRecord[i][3] = List.getString(4);
                DataRecord[i][4] = List.getString(5);
                DataRecord[i][5] = List.getString(6);
                i++;
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - Dvds_Model.detailDataSet() " + Ex.getMessage().toString());
        }
    }

    @Override
    public void showSelectedDataSet(String Value, String Key) {
        String Query;
        String Parse;

        Connex.Connection();
        try {
            Query = "SELECT a.DvdId, a.Title, "
                    + "a.CategoryId, a.Stock, a.Price, a.Detail, b.Description FROM Dvds a, Categories b "
                    + "WHERE a.CategoryId = b.CategoryId ";
            if(Key.equals("DvdId")) {
                Parse = "AND a.DvdId = '" + Value + "'";
            } else if(Key.equals("Title")) {
                Parse = "AND a.Title = '" + Value + "'";
            } else if(Key.equals("CategoryId")) {
                Parse = "AND a.CategoryId = '" + Value + "'";
            } else if(Key.equals("Stock")) {
                Parse = "AND a.Stock = '" + Value + "'";
            } else if(Key.equals("Price")) {
                Parse = "AND a.Price = '" + Value + "'";
            } else if(Key.equals("Detail")) {
                Parse = "AND a.Detail = '" + Value + "'";
            } else if(Key.equals("Description")) {
                Parse = "AND b.Description = '" + Value + "'";
            } else {
                Parse = "";
            }
            Query = Query + Parse;
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            while (List.next()) {
                DvdId = List.getString(1);
                Title = List.getString(2);
                Categories.setCategoryId(List.getString(3));
                Stock = List.getInt(4);
                Price = List.getInt(5);
                Detail = List.getString(6);
                Categories.setDescription(List.getString(7));
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - Dvds_Model.showSelectedDataSet() " + Ex.getMessage().toString());
        }
    }

    public void showSearchDataSet() {
        String Query;
        int i;

        Connex.Connection();
        try {
            Query = "SELECT a.Title, a.DvdId, b.Description, a.Stock, a.Price, a.Detail FROM Dvds a, Categories b "
                    + "WHERE a.CategoryId = b.CategoryId and (a.Title like '%" + Search +"%' or a.Detail like '%"
                    + Search + "%')";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            List.last();
            DataRecord = new String[List.getRow()][6];
            List.beforeFirst();
            i = 0;
            while (List.next()) {
                DataRecord[i][0] = List.getString(1);
                DataRecord[i][1] = List.getString(2);
                DataRecord[i][2] = List.getString(3);
                DataRecord[i][3] = List.getString(4);
                DataRecord[i][4] = List.getString(5);
                DataRecord[i][5] = List.getString(6);
                i++;
            }
            List.close();
            Stat.close();
        } catch(Exception Ex) {
            System.out.println("[ERROR] - Dvds_Model.detailDataSet() " + Ex.getMessage().toString());
        }
    }
    
    @Override
    public boolean addDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "INSERT INTO Dvds VALUES('" + DvdId + "',"
                    + "'" + Title + "','" + Detail + "','" + Price + "','" + Stock + "',"
                    + "'" + Categories.getCategoryId() + "')";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - Dvds_Model.addDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public boolean editDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "UPDATE Dvds SET Title = '" + Title + "', Detail = '" + Detail + "', "
                    + "Price = '" + Price + "', "
                    + "Stock = '" + Stock + "', CategoryId = '" + Categories.getCategoryId() + "' "
                    + "WHERE  DvdId = '" + DvdId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - Dvds_Model.editDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public boolean subDataValue() {
        String Query;

        Connex.Connection();
        try {
            Query = "DELETE FROM Dvds WHERE DvdId = '" + DvdId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - Dvds_Model.subDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }

    @Override
    public String genIdDataValue() {
        String Query;
        String Result;
        int hasil;

        Connex.Connection();
        try {
            Result = null;
            Query = "SELECT DvdId FROM Dvds ORDER BY DvdId DESC LIMIT 1";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            ResultSet List = Stat.executeQuery(Query);
            while (List.next()) {
                Result = List.getString(1).trim();
            }
            List.close();
            Stat.close();

            if (!Result.equals("")) {
                hasil = Integer.parseInt(Result) + 1;
                Result = String.valueOf(hasil);
                if (Result.length() == 1) {
                    Result = "000" + Result;
                } else if (Result.length() == 2) {
                    Result = "00" + Result;
                } else if (Result.length() == 3) {
                    Result = "0" + Result;
                }
            } else {
                Result = "0001";
            }
            return Result;
        } catch (Throwable Ex) {
            System.out.println("[ERROR] - Dvds_Model.genIdDataValue() " + Ex.getMessage());
            return "0001";
        }
    }

    public boolean editBookStock() {
        String Query;

        Connex.Connection();
        try {
            Query = "UPDATE Dvds SET Stock = " + Stock + " WHERE DvdId = '" + DvdId + "'";
            System.out.println("[QUERY] - " + Query);
            Statement Stat = Connex.getConnector().createStatement();
            Stat.executeUpdate(Query);
            Stat.close();
            return true;
        } catch (SQLException Ex) {
            System.out.println("[ERROR] - Dvds_Model.subDataValue() " + Ex.getMessage().toString());
            return false;
        }
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Tester Code">
//    public void methodTester(int flag) {
//        switch (flag) {
//            case 0:
//                showDataSet();
//                System.out.println("Result = " + this.DataRecord[0][1]);
//                break;
//            case 1:
//                showSelectedDataSet("0001", "BooksId");
//                System.out.println("Result = " + this.DateReleased);
//                break;
//            case 2:
//                System.out.println("Result = " + addDataValue());
//                break;
//            case 3:
//                System.out.println("Result = " + editDataValue());
//                break;
//            case 4:
//                System.out.println("Result = " + subDataValue());
//                break;
//            case 5:
//                System.out.println("Result = " + genIdDataValue());
//                break;
//        }
//    }
//
//    public static void main(String[] args) {
////        Books_Model Me = new Books_Model("0001", "2009/09/09", "00985628", "Anchor", "Acong", "PT UD", 200000, 100, 0, "01");
//        Books_Model Me = new Books_Model();
//        Me.methodTester(1);
//    }
    // </editor-fold>
}
// End of Code